#include "excelgeneration.h"
#include "BasicExcel.hpp"
#include <windows.h>
#include <QDesktopServices>
#include <QUrl>
#include <QDir>
#include <QThread>
#include <QDate>
#include <QSqlQueryModel>
#include <QSqlRecord>
#include <QDebug>

#define ROW_BEGIN 5
#define COL_BEGIN 4
ExcelGeneration::ExcelGeneration()
{

}
void ExcelGeneration::ReportSalarySumary(QDate from_date, QDate to_date){

    QString today_str = to_date.toString("yyyyMMdd");
    QDir qDir;

    qDir.mkdir("report");
    qDir.mkdir("report/salary");
    qDir.mkdir("report/salary/" + today_str);
    QString pathDir = "report/salary/" + today_str +"/";
    QString fileName = today_str + "_salary_sumary.xls";
    BasicExcel xls;

    QSqlQueryModel *model = new QSqlQueryModel;
    QString query_str = "SELECT * FROM payroll_view where salary > 0";

    qDebug() << query_str;
    model->setQuery(query_str);


     // create sheet 1 and get the associated BasicExcelWorksheet pointer
    xls.New(1);
    BasicExcelWorksheet* sheet = xls.GetWorksheet(0);

    XLSFormatManager fmt_mgr(xls);
    CellFormat fmt(fmt_mgr);
    BasicExcelCell* cell;

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 4); // row 5 col 2
    cell->Set("Number");
    cell->SetFormat(fmt);
    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 5); // row 5 col 2
    cell->Set("Name");
    cell->SetFormat(fmt);
//    fmt.set_format_string(XLS_FORMAT_GENERAL);
//    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
//    cell = sheet->Cell(5, 4); // row 5 col 2
//    cell->Set("Salary rate");
//    cell->SetFormat(fmt);
//    fmt.set_format_string(XLS_FORMAT_GENERAL);
//    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
//    cell = sheet->Cell(5, 5); // row 5 col 2
//    cell->Set("Cash rate");
//    cell->SetFormat(fmt);
    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 6); // row 5 col 2
    cell->Set("From date");
    cell->SetFormat(fmt);
    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 7); // row 5 col 2
    cell->Set("To date");
    cell->SetFormat(fmt);
    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 8); // row 5 col 2
    cell->Set("Check");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 9); // row 5 col 2
    cell->Set("Tip");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 10); // row 5 col 2
    cell->Set("Cash");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 11); // row 5 col 2
    cell->Set("Check + Tip");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    fmt.set_font(ExcelFont().set_weight(FW_BOLD));
    cell = sheet->Cell(5, 12); // row 5 col 2
    cell->Set("Total");
    cell->SetFormat(fmt);


    int row_model = 0;
    int row_xls = ROW_BEGIN;
    float totalCash = 0;
    float totalCheck = 0;

    for (row_model = 0; row_model < model->rowCount(); row_model++)
    {
        row_xls++;

        fmt.set_font(ExcelFont().set_weight(FW_NORMAL));

        fmt.set_format_string(XLS_FORMAT_INTEGER);
        cell = sheet->Cell(row_xls, COL_BEGIN);
        cell->Set(row_model + 1);
        cell->SetFormat(fmt);

        QString name = model->record(row_model).value("name").toString();
        fmt.set_format_string(XLS_FORMAT_GENERAL);
        cell = sheet->Cell(row_xls, COL_BEGIN + 1);
        cell->Set(name.toStdString().c_str());
        cell->SetFormat(fmt);


        float salaryRate = 0;
        float cashRate= 0;
        float income = 0;
        float salary = 0;
        float cash = 0;
        float check = 0;
        float tip = 0;



        salaryRate = model->record(row_model).value("salary_rate").toDouble() / 10.0;
        cashRate = model->record(row_model).value("cash_rate").toDouble() / 10.0;

        fmt.set_format_string(XLS_FORMAT_DATE);
        cell = sheet->Cell(row_xls, COL_BEGIN + 2);
        cell->Set(from_date.toString("MM-dd-yyyy").toStdString().c_str());
        cell->SetFormat(fmt);

        fmt.set_format_string(XLS_FORMAT_DATE);
        cell = sheet->Cell(row_xls, COL_BEGIN + 3);
        cell->Set(to_date.toString("MM-dd-yyyy").toStdString().c_str());
        cell->SetFormat(fmt);

        income = model->record(row_model).value("salary").toDouble();
        tip = model->record(row_model).value("tip").toDouble();
        cash = model->record(row_model).value("cash").toDouble();
        check = model->record(row_model).value("check").toDouble();
        totalCash += cash;
        totalCheck += check;



        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(row_xls, COL_BEGIN + 4);
        cell->Set(check);
        cell->SetFormat(fmt);

        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(row_xls, COL_BEGIN + 5);
        cell->Set(tip);
        cell->SetFormat(fmt);

        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(row_xls, COL_BEGIN + 6);
        cell->Set(cash);
        cell->SetFormat(fmt);

        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(row_xls, COL_BEGIN + 7);
        cell->Set(tip + check);
        cell->SetFormat(fmt);


        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(row_xls, COL_BEGIN + 8);
        cell->Set(check + cash + tip);
        cell->SetFormat(fmt);
    }

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(row_xls + 1, COL_BEGIN + 6);
    cell->Set(totalCash);
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(row_xls + 1, COL_BEGIN + 7);
    cell->Set(totalCheck);
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(row_xls + 1, COL_BEGIN + 8);
    cell->Set(totalCash + totalCheck);
    cell->SetFormat(fmt);

    xls.SaveAs((pathDir + fileName).toStdString().c_str());
    xls.Close();

    QDesktopServices::openUrl(QUrl(QDir::current().absolutePath().append("/").append(pathDir)));

}

void ExcelGeneration::ReportWorkerIncome(int workerId, QDate from_date, QDate to_date){


    QString today_str = to_date.toString("yyyyMMdd");
    QDir qDir;

    qDir.mkdir("report");
    qDir.mkdir("report/salary");
    qDir.mkdir("report/salary/" + today_str);
    QString pathDir = "report/salary/" + today_str +"/";

    BasicExcel xls;

    QSqlQueryModel *model = new QSqlQueryModel;
    qDebug() << "employee_id = "<<workerId;

    QString query_str = "SELECT * from payroll_view where employee_id =" + QString::number(workerId);
    qDebug() << query_str;
    model->setQuery(query_str);

    xls.New(1);
    BasicExcelWorksheet* sheet = xls.GetWorksheet(0);

    XLSFormatManager fmt_mgr(xls);
    CellFormat fmt(fmt_mgr);
    BasicExcelCell* cell;

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(0, 1);
    cell->Set("Name");
    cell->SetFormat(fmt);

    QString name = model->record(0).value("name").toString();
    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(1, 1);
    cell->Set(name.toStdString().c_str());
    cell->SetFormat(fmt);

    QString fileName = today_str + "_salary_"+ name +".xls";
    fileName = fileName.replace(" ","_");


    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(0, 2);
    cell->Set("From date");
    cell->SetFormat(fmt);


    fmt.set_format_string(XLS_FORMAT_DATE);
    cell = sheet->Cell(1, 2);
    cell->Set(from_date.toString("MM-dd-yyyy").toStdString().c_str());
    cell->SetFormat(fmt);


    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(0, 3);
    cell->Set("To date");
    cell->SetFormat(fmt);


    fmt.set_format_string(XLS_FORMAT_DATE);
    cell = sheet->Cell(1, 3);
    cell->Set(to_date.toString("MM-dd-yyyy").toStdString().c_str());
    cell->SetFormat(fmt);


//    fmt.set_format_string(XLS_FORMAT_GENERAL);
//    cell = sheet->Cell(0, 4);
//    cell->Set("Salary rate");
//    cell->SetFormat(fmt);

    float salaryRate = model->record(0).value("salary_rate").toDouble() /  10.0;
//    fmt.set_format_string(XLS_FORMAT_DECIMAL);
//    cell = sheet->Cell(1, 4);
//    cell->Set(salaryRate);
//    cell->SetFormat(fmt);

//    fmt.set_format_string(XLS_FORMAT_GENERAL);
//    cell = sheet->Cell(0, 5);
//    cell->Set("Cash rate");
//    cell->SetFormat(fmt);

    float cashRate = model->record(0).value("cash_rate").toDouble() / 10.0;
//    fmt.set_format_string(XLS_FORMAT_DECIMAL);
//    cell = sheet->Cell(1, 5);
//    cell->Set(cashRate);
//    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(0, 4);
    cell->Set("Last paid date");
    cell->SetFormat(fmt);

    QDate lastPaidDate = model->record(0).value("end_at").toDate();


    fmt.set_format_string(XLS_FORMAT_DATE);
    cell = sheet->Cell(1, 4);
    cell->Set(lastPaidDate.toString("MM-dd-yyyy").toStdString().c_str());
    cell->SetFormat(fmt);


    int row_model = 0;
    int col_xls = 1;
    float total = 0;
    float totalTip = 0;

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN, col_xls);
    cell->Set("Date");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN + 1, col_xls);
    cell->Set("Income");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN + 2, col_xls);
    cell->Set("Tip");
    cell->SetFormat(fmt);




    for (row_model = 0; row_model < model->rowCount(); row_model++)
    {
        col_xls++;
        QDate createdDate = model->record(row_model).value("created_at").toDate();

        fmt.set_font(ExcelFont().set_weight(FW_NORMAL));

        fmt.set_format_string(XLS_FORMAT_DATE);
        cell = sheet->Cell(ROW_BEGIN, col_xls);
        cell->Set(createdDate.toString("MM-dd-yyyy").toStdString().c_str());
        cell->SetFormat(fmt);

        float income = model->record(row_model).value("salary").toDouble();
        total += income;
        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(ROW_BEGIN + 1, col_xls);
        cell->Set(income);
        cell->SetFormat(fmt);

        float tip = model->record(row_model).value("tip").toDouble();
        totalTip += tip;
        fmt.set_format_string(XLS_FORMAT_DECIMAL);
        cell = sheet->Cell(ROW_BEGIN + 2, col_xls);
        cell->Set(tip);
        cell->SetFormat(fmt);




    }

    float cash = cashRate * (total * salaryRate);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN, col_xls + 1);
    cell->Set("Total");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(ROW_BEGIN + 1, col_xls + 1);
    cell->Set(total);
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(ROW_BEGIN + 2, col_xls + 1);
    cell->Set(totalTip);
    cell->SetFormat(fmt);



    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN + 4, 1);
    cell->Set("Check");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(ROW_BEGIN + 4, 2);
    cell->Set(total * salaryRate - cash);
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN + 5, 1);
    cell->Set("Tip");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(ROW_BEGIN + 5, 2);
    cell->Set(totalTip);
    cell->SetFormat(fmt);


    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN + 6, 1);
    cell->Set("Cash");
    cell->SetFormat(fmt);


    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(ROW_BEGIN + 6, 2);
    cell->Set(cash);
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_GENERAL);
    cell = sheet->Cell(ROW_BEGIN + 7, 1);
    cell->Set("Check + Tip");
    cell->SetFormat(fmt);

    fmt.set_format_string(XLS_FORMAT_DECIMAL);
    cell = sheet->Cell(ROW_BEGIN + 7, 2);
    cell->Set(totalTip + (total * salaryRate - cash));
    cell->SetFormat(fmt);



    xls.SaveAs((pathDir + fileName).toStdString().c_str());
    xls.Close();

    QDesktopServices::openUrl(QUrl(QDir::current().absolutePath().append("/").append(pathDir)));
}
